﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace MusicDatabase
{
    /*
     * Auteur:  Joris Molnár en Niels van Leusden
     * Datum:   31-1-2013
     * Omschrijving: Deze klasse communiceert tussen AddAlbum en SQLServices.
     *               Maakt het mogelijk om alle functies van AddAlbum ook daadwerkelijk te kunnen uitvoeren op de database.
     *               En om albums geheel uit de database te kunnen verwijderen.
     *               
     */
    public class Album
    {
        private int albumID;

        public int AlbumID
        {
            get { return albumID; }
        }

        private string name;
        private string artist;
        private int date;
        private string price;
        private string cover;
        private DataMode dataMode;

        public Album()
        {
        }

        public Album(int albumID)
        {
            this.dataMode = DataMode.raadplegen;
            string[] albumData = this.GetAlbum(albumID);
            this.albumID = albumID;
            this.name = albumData[1];
            this.date = Convert.ToInt32(albumData[2]);
            this.price = albumData[3];
            this.cover = albumData[4];
            this.artist = albumData[5];
        }

        public Album(string[] albumData)
        {
            this.dataMode = DataMode.toevoegen;
            this.albumID = -1;
            this.name = albumData[0];
            this.artist = albumData[1];
            this.price = albumData[2];
            this.cover = albumData[3];
            this.date = Convert.ToInt32(albumData[4]);
        }

        /// <summary>
        /// Return information about an album
        /// </summary>
        /// <param name="albumID"></param>
        /// <returns></returns>
        public string[] GetAlbum(int albumID)
        {
            SQLServices sqlService = new SQLServices();
            string command = "SELECT album.alb_id, album.alb_name, album.alb_date, album.alb_price, album.alb_cover, artist.art_name, CONVERT(nvarchar, SUM(song.song_length)/60) + ':' + CASE WHEN SUM(song.song_length)%60<10 THEN '0' + CONVERT(nvarchar, SUM(song.song_length)%60) ELSE CONVERT(nvarchar, SUM(song.song_length)%60) END " +
                "FROM album, artist, song, songrelease " +
                "WHERE album.alb_artist = artist.art_id AND album.alb_id = " + albumID + " AND songrelease.song_id = song.song_id AND album.alb_id = songrelease.alb_id " +
                "GROUP BY album.alb_id, alb_name, alb_date, alb_price, album.alb_cover, art_name";
            
            List<string[]> table = sqlService.GetTable(command);
            if (table.Count() == 2)
            {
                return table[1];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// Delete albums
        /// </summary>
        /// <param name="albumID">A list of albumID's to delete</param>
        public void DeleteAlbums(List<int> albumID)
        {
            if (albumID.Count() == 0) return;

            SQLServices sqlService = new SQLServices();

            Song song = new Song(); // Delete songs first, this will delete the connection in songrelease too.
            string command = "SELECT DISTINCT song_id FROM songrelease WHERE alb_id = " + albumID[0]; 
            for (int i = 1; i < albumID.Count(); i++)
            {
                command = command + " OR alb_id = " + albumID[i];
            }
            List<string[]> songList = sqlService.GetTable(command);

            command = "DELETE FROM song " +
                "WHERE song_id = " + songList[1][0];
            for (int i = 2; i < songList.Count(); i++)
            {
                command = command + " OR song_id = " + songList[i][0];
            }
            sqlService.Delete(command);

            // Now delete the albums
            command = "DELETE FROM album " +
                "WHERE album.alb_id = " + albumID[0];
            for (int i = 1; i < albumID.Count(); i++)
            {
                command = command + " OR album.alb_id = " + albumID[i];
            }

            sqlService.Delete(command);
        }

        /// <summary>
        /// Formulate the insert command
        /// </summary>
        /// <param name="tabelnaam"></param>
        /// <param name="veldnamen"></param>
        /// <param name="velden"></param>
        /// <returns></returns>
        public string InsertCommand(string tabelnaam, string veldnamen, string velden)
        {
            return "INSERT INTO " + tabelnaam + "  (" + veldnamen + ") VALUES (" + velden + ") ";
        }


        private string veldnamen = "alb_name, alb_price, alb_date";

        /// <summary>
        /// in geval van een nieuwe artist (dataMode == toevoegen):
        /// -- controle of de artist nieuw is
        /// -- toevoegen artist
        /// -- ophalen artist voor het toegekende "artistID"
        /// </summary>
        /// <param name="dm">DataMode: Geeft aan op welke wijze het object gesaved moet worden.</param>
        public void Save()
        {
            SQLServices sqlService = new SQLServices();
            if (this.dataMode == DataMode.toevoegen && !this.Exist(sqlService))
            {
                string velden = "'" + name + "'," + price + "," + date;
                string command = InsertCommand("ALBUM", veldnamen, velden);
                sqlService.Insert(command);
                Exist(sqlService); // haalt het record opnieuw op voor het albumID...
            }
            if (this.dataMode == DataMode.toevoegen)
            {
                albumID = Convert.ToInt32(sqlService.GetVeldValue("alb_id"));
            }
            this.dataMode = DataMode.raadplegen;
        }

        /// <summary>
        /// Add a cover to an album
        /// </summary>
        /// <param name="cover">The filepath to the cover</param>
        /// <param name="albumID">The album to add the cover too</param>
        public void AddCover(string cover, int albumID)
        {
            SQLServices sqlService = new SQLServices();
            dataMode = DataMode.wijzigen;
            string command = "UPDATE album SET alb_cover = '" + cover + "' WHERE alb_id = " + albumID;
            sqlService.Update(command);
        }
        
        /// <summary>
        /// See if an album exist
        /// </summary>
        /// <param name="sqlService"></param>
        /// <returns></returns>
        private bool Exist(SQLServices sqlService)
        {
            string command = "SELECT * FROM album WHERE alb_name = '" + name.Replace("'", "''") + "' AND alb_date = " + date;
            return (sqlService.Exist(command));
        }

        /// <summary>
        /// See if an album exist
        /// </summary>
        /// <param name="name"></param>
        /// <param name="date"></param>
        /// <returns></returns>
        public bool Exist(string name, string date)
        {
            SQLServices sqlService = new SQLServices();
            string command = "SELECT * FROM album WHERE alb_name = '" + name.Replace("'", "''") + "' AND alb_date = " + date; // Other name en date than the global
            return (sqlService.Exist(command));
        }
    }
}
